﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SQLite;

namespace My.Utils
{
    public class SqliteHelper
    {
        public SqliteHelper(string connection)
        {
            connectionString = connection;
        }
        private string connectionString;

        private SQLiteConnection connectionObject;
        public SQLiteConnection GetConnection()
        {
            connectionObject= new SQLiteConnection(connectionString);
            return connectionObject;
        }
        public void CloseConnection()
        {
            connectionObject.Clone();
        }
        public void BuildParameters(SQLiteCommand command, SQLiteParameter[] parameters)
        {
            if (parameters != null)
            {
                foreach (SQLiteParameter param in parameters)
                {
                    command.Parameters.Add(param);
                }

            }

        }
        public void ExecuteNonQuery(string sql, SQLiteParameter[] parameters)
        {
            SQLiteConnection con = GetConnection();
            SQLiteCommand command = new SQLiteCommand(con);
            command.CommandText = sql;
            BuildParameters(command, parameters);
            try
            {
                con.Open();
                command.ExecuteNonQuery();
            }
            finally { 
                con.Close();
            }
            
        }

        public SQLiteDataReader ExecuteReader(string sql, SQLiteParameter[] parameters)
        {
            SQLiteConnection con = GetConnection();
            SQLiteCommand command = new SQLiteCommand(con);
            command.CommandText = sql;
            BuildParameters(command, parameters);
            try
            {
                con.Open();
                return command.ExecuteReader();
            }
            catch(Exception ex) {
                con.Close();
                throw (ex);
            }
            
            
        }

        public string ExecuteScalar(string sql, SQLiteParameter[] parameters)
        {
            SQLiteConnection con = GetConnection();
            SQLiteCommand command = new SQLiteCommand(con);
            command.CommandText = sql;
            BuildParameters(command, parameters);
            string result = string.Empty;
            try
            {
                con.Open();
                result = Convert.ToString(command.ExecuteScalar());
            }
            finally { 
                con.Close();
            }
            
           
            return result;
        }

        public DataSet ExecuteDataSet(string sql, SQLiteParameter[] parameters)
        {
            SQLiteConnection con = GetConnection();
            SQLiteCommand command = new SQLiteCommand(con);
            command.CommandText = sql;
            BuildParameters(command, parameters);
            SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
            DataSet ds = new DataSet();
            try
            {
                con.Open();
                adapter.Fill(ds);
            }
            finally { 
                con.Close();
            }

            return ds;
        }

    }
}
